<?php

# xferlog php script by Romuald AIREY
# http://www.airey.org

# xferlog-update.php is a simple program to inject the log into a database

# XFERLOG Format Details
# ---------------------
# current-time : Mon July 7 02:50:02 2014
# transfer-time : seconds
# remote-host : IPV4 or IPV6
# file-size :  size of transferred file in bytes
# file-name : name ot the file
# transfer-type : a = ascii; b = binary
# special-action-flag C = compressed; U = uncompressed; T = tar'ed; _ = no action 
# direction o = outgoing; i = incoming; d = delete
# access-mode a = anonymous; r = real
# username 
# service-name : usually ftp
# authentication-method : 0 = none; 1 = RFC931 Authentication
# authenticated-user-id : user id or *
# completion-status : c = complete; i = incomplete


# CONFIGURATION
# MYSQL CONNECTION

$dbname = "dbname"; // database name 
$dbhost = "localhost"; // db hostname
$dbuser = "username"; // db username
$dbpass = "password"; // db password

# Name of the data table to inject the file
$mysql_xferlog_data = 'xferlog';

# path og the xferlog file
$xferlog_file = '/var/log/xferlog';

# MYSQL CONNECTION
$conn = mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

// Fecth the last timelog record
$sql = "select max(time_log) as time_log from ".$mysql_xferlog_data;
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0) 
{
    $tab = mysql_fetch_array($result);
    $lastlog = $tab[time_log];
}

# Parse of the file

$handle = fopen($xferlog_file, 'r');
$i = 0;
if ($handle)
{
	while (!feof($handle))
	{
            $line = fgets($handle);
            $columndata = preg_split("/ /", $line);
            $datetime_log = $columndata[0]." ".$columndata[1]." ".$columndata[2]." ".$columndata[3]." ".$columndata[4];
            // Tranform to YYYY-MM-DD HH-MM-SS Mysql Datetimeformat
            $date_mysql = date('Y-m-d H:m:s',strtotime($datetime_log));
            if ($date_mysql > $lastlog) {
            # Dump the column for easy modification if your xferlog format has not the same format
            # [0]DDD [1]MMM [2]dd [3]hh:mm:ss [4]YYYY    [5]transfer-time   [6]remote-host   [7]file-size   
            # [8]filename   [9]transfer-type   [10]special-action-flag   [11]direction   [12]access-mode   [13]username   
            # [14]service-name   [15]authentication-method   [16]authenticated-user-id  [17]completion-status
            $transfert_timetransfert_time = $columndata[5];
            $remote_host = $columndata[6];
            $file_size = $columndata[7];
            $file_name = $columndata[8];
            $transfer_type = $columndata[9];
            $special_action_flag = $columndata[10];
            $direction = $columndata[11];
            $access_mode = $columndata[12];
            $username = $columndata[13];
            $service_name = $columndata[14];
            $authentication_method = $columndata[15];
            $authentication_method_id = $columndata[16];
            $completion_status = $columndata[17];
            
            $sql_insert = "insert into ".$mysql_xferlog_data." (time_log, transfert_time, remote_host, file_size, file_name, transfer_type, special_action_flag, 
                direction, access_mode, username, service_name,  authentication_method, authenticated_user_id, completion_status)
                values (\"".$date_mysql."\",".$transfert_timetransfert_time.",\"".$remote_host."\",".$file_size.",
                \"".$file_name."\",\"".$transfer_type."\",\"".$special_action_flag."\",\"".$direction."\"
                ,\"".$access_mode."\",\"".$username."\",\"".$service_name."\",\"".$authentication_method."\",\"".$authentication_method_id."\"
                ,\"".$completion_status."\"
                )";
            mysql_query($sql_insert);
            $i++;
            }
        }
}

echo "$i lines added to the database";

?>